package cn.tedu;

import org.junit.Test;

import java.sql.*;

public class Demo03 {
    @Test
    public void test1() throws SQLException {
        //插入数据
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
        Statement s=conn.createStatement();
        s.execute("insert into emp(empno,ename)values (101,'tom')");
        conn.close();
        System.out.println("执行完毕！！！");
    }
    @Test
    public void test2() throws SQLException {
        //修改数据
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
        Statement s=conn.createStatement();
        s.execute("update emp set ename= 'jarry'where ename='tom'");
        conn.close();
        System.out.println("执行完毕！！");
    }
    @Test
    public void test3() throws SQLException {
        //删除数据
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
        Statement s=conn.createStatement();
        s.execute("delete from emp where ename='jarry'");
        conn.close();
        System.out.println("执行完毕！！！");
    }
    @Test
    public void test4() throws SQLException {
        //执行查询sql
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
        Statement s=conn.createStatement();
        //结果集对象 里面装着查询回来的所有数据
        ResultSet rs=s.executeQuery("select ename,sal from emp");
        //遍历集合中的所有数据
        while (rs.next()){
            String name=rs.getString("ename");
            double sal=rs.getDouble("sal");
            System.out.println(name+":"+sal);
        }
    }
    @Test
    public void test5() throws SQLException {
        //查询出所有的部门名和地址
        Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&serverTimezone=Asia/Shanghai","root","123456");
        Statement s=conn.createStatement();
        ResultSet rs=s.executeQuery("select * from dept");
        while (rs.next()){
//            从结果集中获取数据两种方式：1.通过字段名2.通过字段位置
//            String dname=rs.getString(2);
//            String loc=rs.getString(3);

            String dname=rs.getString("dname");
            String loc=rs.getString("loc");
            System.out.println(dname+":"+loc);
        }
        conn.close();
    }
}
